package cn.edu.zut.cs.impl;

import cn.edu.zut.cs.dao.StudentDao;
import cn.edu.zut.cs.model.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    static Connection conn;
    //    PreparedStatement pst;
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ssm", "root", "1234");


        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //列表是当作一个数据库
    List<Student> students;

    public StudentDaoImpl() {
        students = new ArrayList<>();
    }

    //删除
    @Override
    public void deleteStudent(Student student) {
        String sql = "delete from student where rollNo=" + student.getRollNo();
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        System.out.println("Student: Roll No " + student.getRollNo()
                + ", deleted from database");
    }

    //从数据库中检索学生名单
    //获取全部
    @Override
    public List<Student> getAllStudents() throws SQLException {
        String sql = "select * from student";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        students = new ArrayList<>();
        while (rs.next()) {
            Student st = new Student(rs.getString("name"), rs.getInt("rollNo"));
            students.add(st);
        }
        rs.close();
        return students;
    }

    //查询
    @Override
    public Student getStudent(int rollNo) throws SQLException {
        String sql = "select * from student where rollNo=" + rollNo;
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        if (rs.next()) {
            Student st = new Student(rs.getString("name"), rs.getInt("rollNo"));
            rs.close();
            return st;
        } else {
            rs.close();
            return null;
        }
    }
    //更新 修改学生
    @Override
    public void updateStudent(Student student) {
        String sql = "update student set name = '" + student.getName() + "' where rollNo = " + student.getRollNo();
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        System.out.println("Student: Roll No " + student.getRollNo()
                + ", updated in the database");
    }

    //增加学生
    @Override
    public void addStudent(Student student) {
        String sql = "insert into student (rollNo, name) values(" + student.getRollNo() + " ,'" + student.getName() + "')";
        System.out.println(sql);
        try {
            Statement stmt = conn.createStatement();

            stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
